/*
 * Copyright 2007. Mount Sinai Hospital, Toronto, Canada.
 * 
 * Licensed under the GNU Lesser General Public License, Version 2. You
 * can find a copy of the license at:
 * 
 * http://www.gnu.org/licenses/old-licenses/gpl-2.0.txt
 * 
 * IN NO EVENT SHALL MOUNT SINAI HOSPITAL BE LIABLE TO ANY PARTY FOR DIRECT, 
 * INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST 
 * PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, 
 * EVEN IF MOUNT SINAI HOSPITAL HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH 
 * DAMAGE.
 * 
 * MOUNT SINAI HOSPITAL SPECIFICALLY DISCLAIMS ANY IMPLIED WARRANTIES OF 
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE AND 
 * ACCOMPANYING DOCUMENTATION, IF ANY, PROVIDED HEREUNDER IS PROVIDED "AS IS". 
 * MOUNT SINAI HOSPITAL HAS NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, 
 * UPDATES, ENHANCEMENTS, OR MODIFICATIONS. 
 */
package com.sinai.mshab.server.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import junit.framework.TestCase;

/**
 * A custom {@link TestCase} that provides setUp and tearDown method that setup
 * and clean up the embedded test database.
 * 
 * @author Arthur Kalmenson
 */
public class SQLTestCase extends TestCase {

	/**
	 * The database DRIVER to use to make the database embedded.
	 */
	private final static String DRIVER = "org.h2.Driver";

	/**
	 * The name of the test database.
	 */
	private final static String DB_NAME = "testdb/h2db";

	/**
	 * The URL to the H2 database.
	 */
	private final static String CONNECTION_URL = "jdbc:h2:" + DB_NAME;

	/**
	 * The {@link Connection} to the database.
	 */
	protected static Connection conn = null;

	protected void setUp() throws SQLException {

		// make the database connection.
		createConnection();

		String table1 = "CREATE TABLE TableA(id integer, field1 varchar(255), "
				+ "field2 varchar(255), field3 varchar(255), id2 varchar(255), "
				+ "PRIMARY KEY (id, id2))";
		String table2 = "CREATE TABLE db_Table_B(start_date date, end_date date, "
				+ "documentNumber varchar(255), PRIMARY KEY (documentNumber))";
		String table3 = "CREATE TABLE SimpleTable(field varchar(255))";

		// create the first table.
		executeSQL(table1);

		// create the second table.
		executeSQL(table2);

		// create the third table.
		executeSQL(table3);

	}

	protected void tearDown() throws SQLException {

		// drop all the tables we created so we have a fresh start.
		PreparedStatement drop = conn.prepareStatement("DROP TABLE TableA");
		drop.execute();
		drop = conn.prepareStatement("DROP TABLE db_Table_B");
		drop.execute();
		drop = conn.prepareStatement("DROP TABLE SimpleTable");
		drop.execute();

		// close the database connection.
		shutdown();
	}

	/**
	 * Cleans the {@link PreparedStatement} output by removing the section
	 * referencing the object. The value returned is just the SQL query itself.
	 * 
	 * @param statement
	 *            the PreparedStatement.
	 * @return just the SQL code from a PreparedStatement's toString().
	 */
	protected String cleanStatementToString(PreparedStatement statement) {

		String statementSQL = statement.toString();

		return statementSQL.substring(statementSQL.indexOf(':') + 1).trim();
	}

	/**
	 * Executes the provided String containing SQL and fails the test case if
	 * the SQL statement didn't execute.
	 * 
	 * @param sql
	 *            the SQL to execute.
	 * @throws SQLException
	 *             thrown if any SQLExceptions occur.
	 */
	protected void executeSQL(String sql) throws SQLException {
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.execute();
	}

	/**
	 * Returns the {@link ResultSet} that contains all the contents from
	 * <code>tableName</code>.
	 * 
	 * @param tableName
	 *            the name of the table from where to get information.
	 * @return the {@link ResultSet} that contains all the contents from
	 *         <code>tableName</code>.
	 * @throws SQLException
	 *             if any {@link SQLException}s occur, they will be thrown.
	 */
	protected ResultSet getResultSet(final String tableName)
			throws SQLException {

		String checkSQL = "SELECT * FROM " + tableName;
		PreparedStatement selectStmt = conn.prepareStatement(checkSQL);
		return selectStmt.executeQuery();
	}

	/**
	 * Populates the tables of the database. This allows use to follow the DRY
	 * principle in the SQLUpdate, SQLDelete and SQLSelect tests.
	 * 
	 * @throws SQLException
	 *             throws any SQL exceptions that occur.
	 */
	protected void populateTables() throws SQLException {

		// create the things we want to INSERT into the databases.
		String simpleTableInsert1 = "INSERT INTO "
				+ "SimpleTable (field) VALUES ('value')";
		String simpleTableInsert2 = "INSERT INTO "
				+ "SimpleTable (field) VALUES ('dog')";
		String simpleTableInsert3 = "INSERT INTO "
				+ "SimpleTable (field) VALUES ('cat')";

		String tableAInsert1 = "INSERT INTO TableA "
				+ "(id, id2, field1, field2, field3) VALUES "
				+ "(123156, 'tmp23145', 'valueOfField1', 'value2', 'Mars')";
		String tableAInsert2 = "INSERT INTO TableA "
				+ "(id, id2, field1, field2, field3) VALUES "
				+ "(9876, 'tmp123', 'field', 'value', 'Earth')";
		String tableAInsert3 = "INSERT INTO TableA "
				+ "(id, id2, field1, field2, field3) VALUES "
				+ "(34563, '094tmp', '124.2524', 'yet another value', "
				+ "'Venus')";

		String db_Table_BInsert1 = "INSERT INTO db_Table_B "
				+ "(start_date, end_date, documentNumber) VALUES "
				+ "('2007-12-25', '2110-12-25', 'the end')";
		String db_Table_BInsert2 = "INSERT INTO db_Table_B "
			+ "(start_date, end_date, documentNumber) VALUES "
			+ "('1914-07-28', '1918-11-11', 'WWI')";
		String db_Table_BInsert3 = "INSERT INTO db_Table_B "
			+ "(start_date, end_date, documentNumber) VALUES "
			+ "('1939-09-01', '1945-09-02', 'World War II')";
		String db_Table_BInsert4 = "INSERT INTO db_Table_B "
			+ "(start_date, end_date, documentNumber) VALUES "
			+ "('1950-06-25', '1953-07-27', 'Korean War')";
		String db_Table_BInsert5 = "INSERT INTO db_Table_B "
			+ "(start_date, end_date, documentNumber) VALUES "
			+ "('1001-10-15', '1002-03-15', 'test')";

		// now execute the SQL statements to insert information into the table.
		executeSQL(simpleTableInsert1);
		executeSQL(simpleTableInsert2);
		executeSQL(simpleTableInsert3);
		executeSQL(tableAInsert1);
		executeSQL(tableAInsert2);
		executeSQL(tableAInsert3);
		executeSQL(db_Table_BInsert1);
		executeSQL(db_Table_BInsert2);
		executeSQL(db_Table_BInsert3);
		executeSQL(db_Table_BInsert4);
		executeSQL(db_Table_BInsert5);
	}

	/**
	 * Creates a database connection with the provided static variables.
	 */
	private static void createConnection() {
		try {
			Class.forName(DRIVER).newInstance();

			// Get a connection
			conn = DriverManager.getConnection(CONNECTION_URL);
		} catch (Exception except) {
			except.printStackTrace();
		}
	}

	/**
	 * Close the database connections.
	 * 
	 * @throws SQLException
	 *             throws any SQL exceptions that occur.
	 */
	private static void shutdown() throws SQLException {
		conn.close();
	}
}
